home *** CD-ROM | disk | FTP | other *** search
/ Winzipper / Winzipper_ISO.iso / programming / oracle7 7.2 / DB / UTIL72 / UTLOIDXS.SQL < prev    next >
Encoding:
Text File  |  1995-05-09  |  8.8 KB  |  238 lines
  1. rem 
  2. rem $Header: utloidxs.sql 7020100.1 94/09/23 22:14:41 cli Generic<base> $ oneidxs.sql 
  3. rem 
  4. Rem Copyright (c) 1989 by Oracle Corporation
  5. Rem NAME
  6. REM    UTLOIDXS.SQL
  7. Rem  FUNCTION
  8. Rem    See below
  9. Rem  NOTES
  10. Rem    See below
  11. Rem  MODIFIED
  12. Rem     glumpkin   10/20/92 -  Renamed from ONEIDXS.SQL 
  13. Rem     rlim       04/29/91 -         change char to varchar2 
  14. Rem   Laursen    01/01/91 - V6 to V7 merge
  15. Rem   Porter    09/23/89 - Change to 8-character filenames
  16. Rem   Porter    04/04/89 - Commenting, cleanup
  17. Rem   Porter    03/27/89 - Creation
  18. Rem
  19. Rem-------------------------------------------------------------------   
  20. Rem                           ONEIDXS.SQL 
  21. Rem
  22. Rem    Use this procedure to find out information about how 
  23. Rem    selective columns are. Use it to:
  24. Rem
  25. Rem      1. Identify prospective columns for new indexes
  26. Rem      2. Determine how selective a current index is
  27. Rem      3. Determine whether a current index is useful or not
  28. Rem
  29. Rem    SQL> START ONE_INDEX_STATS TABLE_NAME COLUMN_NAME
  30. Rem
  31. Rem    NOTE: This procedure requires SQLPLUS version 3.0.3.1 or greater,
  32. Rem          in order for the NEW_VALUE statement to be implemented 
  33. Rem          correctly.
  34. Rem-------------------------------------------------------------------
  35.  
  36. Rem 
  37. Rem *** Set up variables ***
  38. Rem
  39. Set Heading Off
  40. Set Verify Off                     
  41. Set Feedback Off
  42. column table_name new_value one_table_name
  43. column column_name new_value one_column_name
  44. SELECT upper('&1') table_name, upper('&2') column_name FROM DUAL;
  45. Set Heading On
  46. Set Verify On
  47. Set Feedback On
  48.  
  49. SET TERMOUT OFF
  50.  
  51. Rem
  52. Rem     Table statistics:
  53. Rem
  54. Rem     If an indexed column has nulls in it, then care must be used
  55. Rem     in analyzing performance of that index:
  56. Rem       1. A query for a null value will do a table-scan, and
  57. Rem          will never use an index.
  58. Rem       2. A query for a non-null value will only have to search
  59. Rem          an index that has no null entries in it.  Thus, no 
  60. Rem          performance will be lost between a table with 100 not-null
  61. Rem          entries and 100 not-null and 1000 null entries, as long
  62. Rem          as queries for not-null values are made.
  63. Rem
  64.  
  65.  
  66. Rem 
  67. Rem *** Create user statistics tables, if not already created ***
  68. Rem
  69. CREATE TABLE INDEX$INDEX_STATS (
  70.   TABLE_NAME        VARCHAR2(30) NOT NULL,
  71.   COLUMN_NAME       VARCHAR2(30) NOT NULL,
  72.   STAT_NAME         VARCHAR2(30) NOT NULL,
  73.   STAT_VALUE        NUMBER   NOT NULL);
  74.  
  75. create table INDEX$BADNESS_STATS
  76.   (table_name            varchar2(30) not null,
  77.    column_name           varchar2(30) not null,
  78.    badness_factor        number(9),
  79.    keys_with_badness     number(9),
  80.    row_percent           number(9,3),
  81.    row_blk_fail          number(9),
  82.    row_blk_succ          number(9),
  83.    key_percent           number(9,3));
  84.  
  85. Rem
  86. Rem *** Get rid of any current lines in the tables ***
  87. Rem
  88. delete from index$index_stats
  89.   where table_name = '&one_table_name' and column_name = '&one_column_name';
  90. delete from index$badness_stats
  91.   where table_name = '&one_table_name' and column_name = '&one_column_name';
  92.  
  93. Rem
  94. Rem *** GET STATISTICS ***
  95. Rem
  96.  
  97. COLUMN RECORDS NEW_VALUE TOT_ROWS;
  98. SELECT COUNT(*) RECORDS 
  99.   FROM &one_table_name;
  100. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  101.                                      'Rows - Total',
  102.                                      &tot_rows);
  103.  
  104. COLUMN NULLS NEW_VALUE TOT_NULLS;
  105. SELECT COUNT(*) NULLS 
  106.   FROM &one_table_name 
  107.   WHERE &one_column_name IS NULL;
  108. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  109.                                      'Rows - Null',
  110.                                      &tot_nulls);
  111.  
  112. COLUMN DISTINCT_KEYS NEW_VALUE TOT_DISTINCT_KEYS
  113. SELECT COUNT(DISTINCT &one_column_name) DISTINCT_KEYS 
  114.   FROM &one_table_name 
  115.   WHERE &one_column_name IS NOT NULL;
  116. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  117.                                      'Total Distinct Keys',
  118.                                      &TOT_DISTINCT_KEYS);
  119.  
  120. COLUMN NOT_NULLS NEW_VALUE TOT_NOT_NULLS;
  121. SELECT &TOT_ROWS - &TOT_NULLS NOT_NULLS 
  122.   FROM DUAL;
  123. INSERT INTO INDEX$INDEX_STATS ('&one_table_name','&one_column_name',
  124.                                'Rows - Not null',
  125.                                &TOT_NOT_NULLS);
  126.  
  127. Rem
  128. Rem     Following are statistics for the distribution of the keys,
  129. Rem     without null values, since null values can perturb the
  130. Rem     statistics, and indexes don't use nulls.
  131. Rem
  132. Rem     Of particular interest here is the average number of 
  133. Rem     rows per key, since this is a general measure of the
  134. Rem     selectivity of the column.
  135. Rem
  136.  
  137. column average  new_value average
  138. column minimum  new_value minimum
  139. column maximum  new_value maximum 
  140. column std_dev  new_value std_dev
  141. SELECT nvl(avg(COUNT(*)),0)      AVERAGE,
  142.        nvl(min(COUNT(*)),0)      MINIMUM,
  143.        nvl(max(COUNT(*)),0)      MAXIMUM ,
  144.        nvl(stddev(count(*)),0)   STD_DEV
  145.   FROM &one_table_name
  146.   WHERE &one_column_name IS NOT NULL
  147.   GROUP BY &one_column_name;
  148. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  149.                                      'Rows per key - avg',&average);
  150. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  151.                                      'Rows per key - min',&minimum);
  152. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  153.                                      'Rows per key - max',&maximum);
  154. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  155.                                      'Rows per key - dev',&std_dev);
  156.  
  157. Rem
  158. Rem      The following table is a histogram of key selectivity in the
  159. Rem      column.  This can be used to determine what the overall 
  160. Rem      'badness' (a technical term) of the column is. NOTE: This
  161. Rem      key selectivity is measured WITHOUT NULLS, since nulls don't
  162. Rem      use indexes.
  163. Rem 
  164. Rem      The 'badness_factor' column tells how many times a key was 
  165. Rem      repeated.
  166. Rem      The 'keys_with_badness' column tells how many keys were
  167. Rem      repeated 'badness_factor' times.
  168. Rem 
  169. Rem      Higher badness factors are detrimental to the selectivity
  170. Rem      of the column.
  171. Rem
  172. Rem      This table takes on different meanings depending upon 
  173. Rem      which access method is assumed:
  174. Rem      1. Access distributed equally across rows
  175. Rem         In this case, the user should look at the 'ROW_PERC' 
  176. Rem         column to determine what percentage of the queries will
  177. Rem         have high badness factors. 
  178. Rem      2. Access distributed equally across key values
  179. Rem         In this case, the user should look at the 'KEY_PERC' 
  180. Rem         column to determine what percentage of the queries will
  181. Rem         have high badness factors.
  182. Rem  
  183.  
  184. drop view index$badness;
  185. create view index$badness as
  186.    (select count(&one_column_name) repeat_count  
  187.    from &one_table_name 
  188.    group by &one_column_name);
  189.  
  190. insert into index$badness_stats (table_name,column_name,
  191.     badness_factor, keys_with_badness, row_percent, 
  192.     row_blk_fail, row_blk_succ, key_percent)
  193.   select '&one_table_name' table_name,'&one_column_name' column_name,
  194.     repeat_count badness_factor,
  195.     count(repeat_count) keys_with_badness,
  196.     (count(repeat_count)*repeat_count/&tot_not_nulls)*100 row_percent,
  197.     (count(repeat_count)*repeat_count*repeat_count) row_blk_fail,
  198.     (count(repeat_count)*repeat_count*ceil(repeat_count/2)) row_blk_succ,
  199.     (count(repeat_count)/&tot_distinct_keys)*100 key_percent
  200.   from index$badness
  201.   where repeat_count <> 0
  202.   group by repeat_count;
  203.  
  204. Rem
  205. Rem     The following two statistics attempt to determine
  206. Rem     how expensive it is to access keys in this table.
  207. Rem     A 'miss' will have to scan all the rows for a key value,
  208. Rem     and a 1-row hit will, on the average, only have to scan
  209. Rem     half of them.
  210. Rem   
  211. Rem     These gets are the the gets to read data from the table.
  212. Rem     Gets needed to access branch and leaf nodes of the index
  213. Rem     are not counted here.
  214. Rem
  215.  
  216. column gets_per_miss_by_row new_value miss_gets
  217. column gets_per_1_row_hit_by_row new_value hit_gets
  218. select nvl((sum(row_blk_fail)/&tot_not_nulls),0) gets_per_miss_by_row,
  219.        nvl((sum(row_blk_succ)/&tot_not_nulls),0) gets_per_1_row_hit_by_row
  220.   from index$badness_stats
  221.   where table_name like '&one_table_name' AND
  222.         column_name like '&one_column_name';  
  223. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  224.                                      'db_gets_per_key_miss',&miss_gets);
  225. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  226.                                      'db_gets_per_key_hit',&hit_gets);
  227.  
  228. drop view index$badness;
  229. SET TERMOUT ON
  230.  
  231. Rem
  232. Rem Clean up
  233. Rem 
  234. undefine 1
  235. undefine 2
  236. undefine one_table_name
  237. undefine one_column_name
  238.